package com.yifi.common;


import java.io.*;
import java.lang.reflect.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.text.SimpleDateFormat;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jfinal.plugin.activerecord.Record;


public class POIExcel {

	private static POIFSFileSystem fs;
    private static HSSFWorkbook wb;
    private static HSSFSheet sheet;
    private static HSSFRow row;
    
	//前台数据集导出
	public static void exportExcel(String fpath, JSONArray jsonCol, JSONArray jsonRow) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("Sheet1");
		
		// 标题行
		  HSSFCellStyle styleC = workbook.createCellStyle();  //样式
	      HSSFFont fontC = workbook.createFont();  
	      fontC.setBold(true);
	      styleC.setFont(fontC);  
	      styleC.setAlignment(HorizontalAlignment.CENTER);  
	      
		HSSFRow row1 = sheet.createRow(0);
		for (int i = 0; i < jsonCol.size(); i++) {
			JSONObject json = jsonCol.getJSONObject(i);
			String title = "";
			if (json.containsKey("title")) {
				title = json.getString("title");
			}
			if (json.containsKey("width")) {
				Double width = json.getDouble("width");
				sheet.setColumnWidth(i, (short) (35.7 * width));  //列宽度
			}

			HSSFCell cell = row1.createCell(i);
			cell.setCellStyle(styleC );
			cell.setCellValue(new HSSFRichTextString(title));
		}
		// 数据行
		HSSFCellStyle styleR = workbook.createCellStyle();  //样式
		for (int i = 0; i < jsonRow.size(); i++) {
			HSSFRow row = sheet.createRow(i + 1);
			JSONObject jsonR = jsonRow.getJSONObject(i);
			for (int j = 0; j < jsonCol.size(); j++) {
				JSONObject jsonC = jsonCol.getJSONObject(j);
				String field = jsonC.getString("field");
				if (jsonR.containsKey(field)) {
					String cvalue = jsonR.getString(field);
					cvalue = cvalue == "null" ? "" : cvalue;

					styleR.setAlignment(HorizontalAlignment.LEFT);  
					if (jsonC.containsKey("align")) {//文字方向
						String align = jsonC.getString("align");
						if(align.equals("center"))
						{
							styleR.setAlignment(HorizontalAlignment.CENTER);  
						}
						if(align.equals("right"))
						{
							styleR.setAlignment(HorizontalAlignment.RIGHT);  
						}
					}
					HSSFCell cell = row.createCell(j);
					cell.setCellStyle(styleR );
					cell.setCellValue(new HSSFRichTextString(cvalue));
				}
			}
		}
		try {
			OutputStream out = new FileOutputStream(fpath);
			workbook.write(out);
			out.close();
			System.out.println("excel导出成功！");
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	//后台数据集导出
	public static void exportExcelData(String exportid, String coldata, List<Record> listRecord) {

		String excelPath = IOHelper.GetUploadPath()+"excel/";
		File dataFile = new File(excelPath);
		if (!dataFile.exists()) {// 创建文件夹
			dataFile.mkdirs();
		}
		String fpath =  excelPath+exportid+".xls";
		JSONArray jsonCol = JSONArray.parseArray(coldata);
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("Sheet1");
		
		// 标题行
		  HSSFCellStyle styleC = workbook.createCellStyle();  //样式
	      HSSFFont fontC = workbook.createFont();  
	      fontC.setBold(true);  
	      styleC.setFont(fontC);  
	      styleC.setAlignment(HorizontalAlignment.CENTER);  
	      
		HSSFRow row1 = sheet.createRow(0);
		for (int i = 0; i < jsonCol.size(); i++) {
			JSONObject json = jsonCol.getJSONObject(i);
			String title = "";
			if (json.containsKey("title")) {
				title = json.getString("title");
			}
			if (json.containsKey("width")) {
				Double width = json.getDouble("width");
				sheet.setColumnWidth(i, (short) (35.7 * width));  //列宽度
			}

			HSSFCell cell = row1.createCell(i);
			cell.setCellStyle(styleC );
			cell.setCellValue(new HSSFRichTextString(title));
		}
		// 数据行
		int rowindex = 0;
		for(Record r:listRecord)
		{
			rowindex ++;
			HSSFRow row = sheet.createRow(rowindex);
			for (int j = 0; j < jsonCol.size(); j++) {
				String field = jsonCol.getJSONObject(j).getString("field");
				if(r.getColumns().containsKey(field))
				{
					String cvalue = "";
					if(r.get(field)!=null)
					{
					cvalue = r.get(field).toString();
					}
					
					HSSFCell cell = row.createCell(j);
					cell.setCellValue(new HSSFRichTextString(cvalue));
				}
			}
		}
		try {
			OutputStream out = new FileOutputStream(fpath);
			workbook.write(out);
			out.close();
			System.out.println("excel导出成功！");
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	//后台数据集导出
	public static void exportExcelData(String fpath, JSONArray jsonCol, List<Record> listRecord) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("Sheet1");
		
		// 标题行
		  HSSFCellStyle styleC = workbook.createCellStyle();  //样式
	      HSSFFont fontC = workbook.createFont();
	      fontC.setBold(true);
	      //fontC.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
	      styleC.setFont(fontC); 
	      styleC.setAlignment(HorizontalAlignment.CENTER);
	      //styleC.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
	      
		HSSFRow row1 = sheet.createRow(0);
		for (int i = 0; i < jsonCol.size(); i++) {
			JSONObject json = jsonCol.getJSONObject(i);
			String title = "";
			if (json.containsKey("title")) {
				title = json.getString("title");
			}
			if (json.containsKey("width")) {
				Double width = json.getDouble("width");
				sheet.setColumnWidth(i, (short) (35.7 * width));  //列宽度
			}

			HSSFCell cell = row1.createCell(i);
			cell.setCellStyle(styleC );
			cell.setCellValue(new HSSFRichTextString(title));
		}
		// 数据行
		int rowindex = 0;
		for(Record r:listRecord)
		{
			rowindex ++;
			HSSFRow row = sheet.createRow(rowindex);
			for (int j = 0; j < jsonCol.size(); j++) {
				String field = jsonCol.getJSONObject(j).getString("field");
				if(r.getColumns().containsKey(field))
				{
					String cvalue = "";
					if(r.get(field)!=null)
					{
					cvalue = r.get(field).toString();
					}
					
					HSSFCell cell = row.createCell(j);
					cell.setCellValue(new HSSFRichTextString(cvalue));
				}
			}
		}
		try {
			OutputStream out = new FileOutputStream(fpath);
			workbook.write(out);
			out.close();
			System.out.println("excel导出成功！");
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	
	 /** 
     * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上 
     *  
     * @param title 
     *            表格标题名 
     * @param headers 
     *            表格属性列名数组 
     * @param dataset 
     *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的 
     *            javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据) 
     * @param out 
     *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中 
     * @param pattern 
     *            如果有时间数据，设定输出格式。默认为"yyy-MM-dd" 
     */  
	  public static void exportExcel(String title, String[] headers,  
	          Collection<T> dataset, OutputStream out, String pattern) {  
	      // 声明一个工作薄  
	      HSSFWorkbook workbook = new HSSFWorkbook();  
	      // 生成一个表格  
	      HSSFSheet sheet = workbook.createSheet(title);  
	      // 设置表格默认列宽度为15个字节  
	      sheet.setDefaultColumnWidth((short) 15);  
	      // 生成一个样式  
	      HSSFCellStyle style = workbook.createCellStyle();  
	      // 设置这些样式  
	      style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);  
	      style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	      style.setBorderBottom(BorderStyle.THIN);  
	      style.setBorderLeft(BorderStyle.THIN);  
	      style.setBorderRight(BorderStyle.THIN);  
	      style.setBorderTop(BorderStyle.THIN);  
	      style.setAlignment(HorizontalAlignment.CENTER);  
	      // 生成一个字体  
	      HSSFFont font = workbook.createFont();  
	      font.setColor(HSSFColor.VIOLET.index);  
	      font.setFontHeightInPoints((short) 12);  
	      font.setBold(true);
	      // 把字体应用到当前的样式  
	      style.setFont(font);  
	      // 生成并设置另一个样式  
	      HSSFCellStyle style2 = workbook.createCellStyle();  
	      style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);  
	      style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	      style2.setBorderBottom(BorderStyle.THIN);  
	      style2.setBorderLeft(BorderStyle.THIN);  
	      style2.setBorderRight(BorderStyle.THIN);  
	      style2.setBorderTop(BorderStyle.THIN);  
	      style2.setAlignment(HorizontalAlignment.CENTER);  
	      style2.setVerticalAlignment(VerticalAlignment.CENTER);  
	      // 生成另一个字体  
	      HSSFFont font2 = workbook.createFont();
	      // 把字体应用到当前的样式  
	      style2.setFont(font2);  

	      // 声明一个画图的顶级管理器  
	      HSSFPatriarch patriarch = sheet.createDrawingPatriarch();  
	      // 定义注释的大小和位置,详见文档  
	      HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,  
	               0, 0, 0, (short) 4, 2, (short) 6, 5));  
	       // 设置注释内容  
	       comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));  
	       // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.  
	       comment.setAuthor("leno");  
	 
	       // 产生表格标题行  
	       HSSFRow row = sheet.createRow(0);  
	       for (short i = 0; i < headers.length; i++) {  
	           HSSFCell cell = row.createCell(i);  
	           cell.setCellStyle(style);  
	           HSSFRichTextString text = new HSSFRichTextString(headers[i]);  
	           cell.setCellValue(text);  
	       }  
	 
	       // 遍历集合数据，产生数据行  
	       Iterator<T> it = dataset.iterator();  
	       int index = 0;  
	       while (it.hasNext()) {  
	           index++;  
	           row = sheet.createRow(index);  
	           T t = (T) it.next();  
	           // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值  
	           Field[] fields = t.getClass().getDeclaredFields();  
	           for (short i = 0; i < fields.length; i++) {  
	               HSSFCell cell = row.createCell(i);  
	               cell.setCellStyle(style2);  
	               Field field = fields[i];  
	               String fieldName = field.getName();  
	               String getMethodName = "get"  
	                       + fieldName.substring(0, 1).toUpperCase()  
	                       + fieldName.substring(1);  
	               try {  
	                   Class tCls = t.getClass();  
	                   Method getMethod = tCls.getMethod(getMethodName,   new Class[] {});  
	                   Object value = getMethod.invoke(t, new Object[] {});  
	                   // 判断值的类型后进行强制类型转换  
	                   String textValue = null;  
	                   // if (value instanceof Integer) {  
	                   // int intValue = (Integer) value;  
	                   // cell.setCellValue(intValue);  
	                   // } else if (value instanceof Float) {  
	                   // float fValue = (Float) value;  
	                   // textValue = new HSSFRichTextString(  
	                   // String.valueOf(fValue));  
	                   // cell.setCellValue(textValue);  
	                   // } else if (value instanceof Double) {  
	                   // double dValue = (Double) value;  
	                   // textValue = new HSSFRichTextString(  
	                   // String.valueOf(dValue));  
	                   // cell.setCellValue(textValue);  
	                   // } else if (value instanceof Long) {  
	                   // long longValue = (Long) value;  
	                   // cell.setCellValue(longValue);  
	                   // }  
	                   if (value instanceof Boolean) {  
	                       boolean bValue = (Boolean) value;  
	                       textValue = "男";  
	                       if (!bValue) {  
	                           textValue = "女";  
	                       }  
	                   } else if (value instanceof Date) {  
	                       Date date = (Date) value;  
	                       SimpleDateFormat sdf = new SimpleDateFormat(pattern);  
	                       textValue = sdf.format(date);  
	                   } else if (value instanceof byte[]) {  
	                       // 有图片时，设置行高为60px;  
	                       row.setHeightInPoints(60);  
	                       // 设置图片所在列宽度为80px,注意这里单位的一个换算  
	                       sheet.setColumnWidth(i, (short) (35.7 * 80));  
	                       // sheet.autoSizeColumn(i);  
	                       byte[] bsValue = (byte[]) value;  
	                       HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,  
	                               1023, 255, (short) 6, index, (short) 6, index);  
	                       anchor.setAnchorType(AnchorType.byId(2));
	                       patriarch.createPicture(anchor, workbook.addPicture(  
	                               bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));  
	                   } else {  
	                       // 其它数据类型都当作字符串简单处理  
	                       textValue = value.toString();  
	                   }  
	                   // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成  
	                   if (textValue != null) {  
	                       Pattern p = Pattern.compile("^//d+(//.//d+)?$");
	                       Matcher matcher = p.matcher(textValue);  
	                       if (matcher.matches()) {  
	                           // 是数字当作double处理  
	                           cell.setCellValue(Double.parseDouble(textValue));  
	                       } else {  
	                           HSSFRichTextString richString = new HSSFRichTextString(  
	                                   textValue);  
	                           HSSFFont font3 = workbook.createFont();  
	                           font3.setColor(HSSFColor.BLUE.index);  
	                           richString.applyFont(font3);  
	                           cell.setCellValue(richString);  
	                       }  
	                   }  
	               } catch (SecurityException e) {  
	                   // TODO Auto-generated catch block  
	                   e.printStackTrace();  
	               } catch (NoSuchMethodException e) {  
	                   // TODO Auto-generated catch block  
	                   e.printStackTrace();  
	               } catch (IllegalArgumentException e) {  
	                   // TODO Auto-generated catch block  
	                   e.printStackTrace();  
	               } catch (IllegalAccessException e) {  
	                   // TODO Auto-generated catch block  
	                   e.printStackTrace();  
	               } catch (InvocationTargetException e) {  
	                   // TODO Auto-generated catch block  
	                   e.printStackTrace();  
	               } finally {  
	                   // 清理资源  
	               }  
	           }  
	 
	       }  
	       try {  
	           workbook.write(out);  
	       } catch (IOException e) {  
	           // TODO Auto-generated catch block  
	           e.printStackTrace();  
	       }  
	 
	   }  
	  
	  //==============2017-03-20==================

		public static List<Map<Integer, String>> readExcelList(InputStream is) {
			List<Map<Integer, String>> content = new ArrayList<Map<Integer, String>>();
			try {
				fs = new POIFSFileSystem(is);
				wb = new HSSFWorkbook(fs);
			} catch (IOException e) {
				e.printStackTrace();
			}
			sheet = wb.getSheetAt(0);
			// 得到总行数
			int rowNum = sheet.getLastRowNum();
			row = sheet.getRow(0);
			int colNum = row.getPhysicalNumberOfCells();
			// 正文内容应该从第二行开始,第一行为表头的标题
			for (int i = 1; i <= rowNum; i++) {
				Map<Integer, String> map = new HashMap<Integer, String>();
				row = sheet.getRow(i);
				if(row==null)
				{
					continue;
				}
				colNum = row.getPhysicalNumberOfCells();
				if(colNum<=0)   //没有行数据跳出
				{
					continue;
				}
				int j = 0;
				while (j <= colNum) {
					// 每个单元格的数据内容用"-"分割开，以后需要时用String类的replace()方法还原数据
					// 也可以将每个单元格的数据设置到一个javabean的属性中，此时需要新建一个javabean
					HSSFCell cell=row.getCell(j);
					if(cell==null)
					{
						map.put(j, "");
					}
					else
					{
						int celltype=cell.getCellType();
						//0 数值类型 1字符串类型 2 公式类型	3 空值类型 4 布尔类型  5错误数据
						switch (celltype) {
						case HSSFCell.CELL_TYPE_NUMERIC:
							if(HSSFDateUtil.isCellDateFormatted(cell)
									||DateUtil.isReserved(cell.getCellStyle().getDataFormat())
									||DateUtil.isDateFormat(cell.getCellStyle().getDataFormatString()))  
						    {
								SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
								String datestr=sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
								map.put(j, datestr);
							}
							else
							{
								// 返回数值类型的值
								Object inputValue = null;// 单元格值
								Long longVal = Math.round(cell.getNumericCellValue());
								Double doubleVal = cell.getNumericCellValue();
								if (Double.parseDouble(longVal + ".0") == doubleVal) { // 判断是否含有小数位.0
									inputValue = longVal;
								} else {
									inputValue = doubleVal;
								}
								//DecimalFormat df = new DecimalFormat("#.####"); // 格式化为四位小数，按自己需求选择；
								map.put(j, String.valueOf(inputValue)); // 返回String类型
							}
							break;
						case HSSFCell.CELL_TYPE_STRING:
							map.put(j, cell.getStringCellValue());
							break;
						case HSSFCell.CELL_TYPE_FORMULA:
							map.put(j, "");
							break;
						case HSSFCell.CELL_TYPE_BLANK:
							map.put(j, "");
							break;
						case HSSFCell.CELL_TYPE_BOOLEAN:
							map.put(j, "");
							break;
						case HSSFCell.CELL_TYPE_ERROR:
							map.put(j, "");
							break;
						}
					}
					j++;
				}
				content.add(map);
			}
			return content;
		}
		/**
		 * 根据HSSFCell类型设置数据
		 * 
		 * @param cell
		 * @return
		 */
		private static String getCellFormatValue(HSSFCell cell) {
			String cellvalue = "";
			if (cell != null) {
				// 判断当前Cell的Type
				switch (cell.getCellType()) {
				// 如果当前Cell的Type为NUMERIC
				case HSSFCell.CELL_TYPE_NUMERIC:
				case HSSFCell.CELL_TYPE_FORMULA: {
					// 判断当前的cell是否为Date
					if (HSSFDateUtil.isCellDateFormatted(cell)) {
						// 如果是Date类型则，转化为Data格式

						// 方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
						// cellvalue = cell.getDateCellValue().toLocaleString();

						// 方法2：这样子的data格式是不带带时分秒的：2011-10-12
						Date date = cell.getDateCellValue();
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
						cellvalue = sdf.format(date);

					}
					// 如果是纯数字
					else {
						// 取得当前Cell的数值
						cellvalue = String.valueOf(cell.getNumericCellValue());
					}
					break;
				}
					// 如果当前Cell的Type为STRIN
				case HSSFCell.CELL_TYPE_STRING:
					// 取得当前的Cell字符串
					cellvalue = cell.getRichStringCellValue().getString();
					break;
				// 默认的Cell值
				default:
					cellvalue = " ";
				}
			} else {
				cellvalue = "";
			}
			return cellvalue;

		}
}
